package Actions;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import forms.degreecollection;

public class showappInfoAction extends Action {
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {

		forms.showappInfoform lo = (forms.showappInfoform) form;
		lo.reset();
		String uname = (String) request.getParameter("username");
		lo.setUsername(uname);

		String url = "jdbc:postgresql://localhost:5432/Applications";
		Connection conn = null;
		Statement stmt = null;
		Statement stmt1 = null;
		Statement stmt2 = null;
		Statement stmt3 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;

		int app = -1;

		try {
			// Registering Postgresql JDBC driver
			Class.forName("org.postgresql.Driver");
			// Open a connection to the database
			conn = DriverManager.getConnection(url, "postgres", "postgres");
			// Create the statement
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			rs = stmt
					.executeQuery("SELECT user_name,APP FROM users WHERE user_name = '"
							+ uname + "'");
			while (rs.next()) {
				if (rs.getInt("APP") > 0) {
					app = rs.getInt("APP");
					// ret = true;
				} else {
					// ret = false;
				}
			}

		} catch (SQLException e) {
			System.out.println(e);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// Registering Postgresql JDBC driver
		try {
			Class.forName("org.postgresql.Driver");
			// Open a connection to the database
			conn = DriverManager.getConnection(url, "postgres", "postgres");
			// Create the statement
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			stmt3 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			String first = null;
			String last = null;
			String middle = null;
			rs = stmt
					.executeQuery("SELECT First, Middle, Last FROM NAME WHERE NameID ='"
							+ app + "'");

			while (rs.next()) {
				first = rs.getString("First");
				middle = rs.getString("Middle");
				last = rs.getString("Last");
			}

			rs = stmt
					.executeQuery("SELECT MainTable.AppID FROM MainTable WHERE NameID = '"
							+ app + "'");
			int meantableAppID = -1;
			while (rs.next()) {
				meantableAppID = rs.getInt("AppID");
			}

			rs = stmt.executeQuery("SELECT * FROM Application WHERE app_id = '"
					+ meantableAppID + "'");
			String status = null;
			while (rs.next()) {
				lo.resetRV();
				lo.setRvCount(0);
				status = rs.getString("status");
				if (rs.getString("Comment1") != null) {
					lo.updateRvcomments(new forms.reviewComments(rs
							.getString("Comment1"), rs.getInt("Grade1")));
					lo.setRvCount(1);
				}
				if (rs.getString("Comment2") != null) {
					lo.updateRvcomments(new forms.reviewComments(rs
							.getString("Comment2"), rs.getInt("Grade2")));
					lo.setRvCount(2);
				}
			}

			String street = null;
			String city = null;
			String lstate = null;
			String zip = null;
			String areaCode = null;
			String pnumber = null;

			rs = stmt
					.executeQuery("SELECT AddID FROM MainTable WHERE NameID = '"
							+ app + "'");
			int maintableaddid = -1;
			while (rs.next()) {
				maintableaddid = rs.getInt("AddID");
			}

			rs = stmt
					.executeQuery("SELECT Street, City, State, Zip, AreaCode, Phone FROM ADDRESS WHERE AddID = '"
							+ maintableaddid + "'");

			while (rs.next()) {
				street = rs.getString("Street");
				city = rs.getString("City");
				lstate = rs.getString("State");
				zip = rs.getString("Zip");
				areaCode = rs.getString("AreaCode");
				pnumber = rs.getString("Phone");
			}
			// System.out.println(status);

			String citizen = null;
			rs = stmt.executeQuery("SELECT Country FROM Countries WHERE ID=  "
					+ "(SELECT Citizenship FROM COUNTRY WHERE CounID = "
					+ "(SELECT distinct CounID FROM MainTable WHERE NameID = "
					+ "(SELECT NAME.NAMEID FROM NAME WHERE First = '" + first
					+ "' AND Last = '" + last + "')))");

			while (rs.next()) {
				citizen = rs.getString("Country");
			}

			String residency = null;
			rs = stmt
					.executeQuery("SELECT Residency FROM COUNTRY WHERE CounID = "
							+ "(SELECT distinct CounID FROM MainTable WHERE NameID = "
							+ "(SELECT NAME.NAMEID FROM NAME WHERE First = '"
							+ first + "' AND Last = '" + last + "'))");

			while (rs.next()) {
				residency = rs.getString("Residency");
			}

			String residence = null;
			rs = stmt.executeQuery("SELECT Country FROM Countries WHERE ID=  "
					+ "(SELECT Residence FROM COUNTRY WHERE CounID = "
					+ "(SELECT distinct CounID FROM MainTable WHERE NameID = "
					+ "(SELECT NAME.NAMEID FROM NAME WHERE First = '" + first
					+ "' AND Last = '" + last + "')))");

			while (rs.next()) {
				residence = rs.getString("Country");
			}

			String specialization = null;
			rs = stmt
					.executeQuery("SELECT Specialization FROM Specializations WHERE ID=  "
							+ "(SELECT distinct SpecID FROM MainTable WHERE NameID = "
							+ "(SELECT NAME.NAMEID FROM NAME WHERE First = '"
							+ first + "' AND Last = '" + last + "'))");

			while (rs.next()) {
				specialization = rs.getString("Specialization");
			}

			rs1 = stmt1
					.executeQuery("SELECT DegID FROM MainTable WHERE NameID = '"
							+ app + "'");
			
			lo.clearCollection();
			while (rs1.next()) {

				rs = stmt.executeQuery("SELECT * FROM DEGREE WHERE DegID= '"
						+ rs1.getInt("DegID") + "'");
				rs2 = stmt2
						.executeQuery("SELECT Location, University FROM Universities WHERE ID = "
								+ "(SELECT School FROM DEGREE WHERE DegID=  '"
								+ rs1.getInt("DegID") + "')");

				rs3 = stmt3
						.executeQuery("SELECT Discipline FROM Disciplines WHERE ID = "
								+ "(SELECT Major FROM DEGREE WHERE DegID=  '"
								+ rs1.getInt("DegID") + "')");
				
				while (rs.next() && rs2.next() && rs3.next()) {
					forms.degreecollection newDegree = new degreecollection(
							rs2.getString("Location"),
							rs2.getString("University"),
							rs3.getString("Discipline"), rs.getString("Month"),
							rs.getString("Year"), rs.getString("GPA"),
							rs.getString("Title"));
					lo.updateCollection(newDegree);
				}
			}

			lo.setSpecialization(specialization);
			lo.setResidence(residence);
			lo.setResidency(residency);
			lo.setCitizen(citizen);
			lo.setFirst(first);
			lo.setLast(last);
			lo.setMiddle(middle);
			lo.setAreaCode(areaCode);
			lo.setStatus(status);
			lo.setCity(city);
			lo.setZip(zip);
			lo.setLstate(lstate);
			lo.setPnumber(pnumber);
			lo.setStreet(street);

			rs.close();
			stmt.close();
			rs2.close();
			stmt2.close();
			rs3.close();
			stmt3.close();
			conn.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		System.out.println(request.getParameter("username"));
		return mapping.findForward("show");
	}
}
